//
//  DialectDB.m
//  DialectShare
//
//  Created by timmy zhong on 3/20/12.
//  Copyright (c) 2012 __MyCompanyName__. All rights reserved.
//

#import "DialectDB.h"
#import "FMDatabase.h"
#import "PathHelper.h"
#import "Const.h"
#import "Dialect.h"
#import "YuXi.h"

@interface DialectDB ()
- (NSArray *)select:(NSString *)select condition:(NSString *)condition orderBy:(NSString *)orderBy start:(NSInteger)start count:(NSInteger)count;
@end

@implementation DialectDB

- (id)init
{
    self = [super init];
    if (self) {
        FMDatabase *db = [FMDatabase databaseWithPath:[PathHelper dbPath]];
        if ([db open]) {
            NSString* createTableStatement = [NSString stringWithFormat:
                                              @"""CREATE TABLE IF NOT EXISTS %@"""
                                              """(%@ INTEGER PRIMARY KEY, %@ INTEGER, %@ REAL, %@ REAL, %@ TEXT, %@ TEXT, %@ INTEGER, %@ INTEGER, %@ INTEGER, %@ INTEGER, %@ INTEGER, %@ INTEGER, %@ TEXT, %@ TEXT, %@ TEXT)""", DialectTableName, DialectId, DialectUid, DialectLongitude, DialectLatitude, DialectAddress, DialectUri, DialectDateline, DialectGood, DialectBad, DialectListen, DialectYuXiId, DialectSentenceId, DialectDistance, DialectYuXi, DialectContent];
            BOOL su = [db executeUpdate:createTableStatement]; 
            if (!su) {
                NSLog(@"fail to create table  %@  error  %@", DialectTableName, [db lastErrorMessage]);
            }
            [db close];
        }

    } 
    
    return self;
}

- (void)addDialects:(NSArray *)dialects
{
    FMDatabase *db = [FMDatabase databaseWithPath:[PathHelper dbPath]];
    if (![db open]) {
        NSLog(@"addDialects: fail to open DB error  %@", [db lastErrorMessage]);
        return;
    }
    
    [db beginTransaction];
    for (Dialect *dialect in dialects) {
    
        NSString *sql = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@ (%@, %@, %@, %@, %@, %@, %@, %@, %@, %@, %@, %@, %@, %@, %@) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", DialectTableName , DialectId, DialectUid, DialectLongitude, 
                         DialectLatitude, DialectAddress, DialectUri, DialectDateline,
                         DialectGood, DialectBad, DialectListen, DialectYuXiId, 
                         DialectSentenceId, DialectDistance, DialectYuXi, DialectContent];
        [db executeUpdate:sql,
         [NSNumber numberWithInt:dialect.dialectId],
         [NSNumber numberWithInt:dialect.uid],
         [NSNumber numberWithFloat:dialect.longitude],
         [NSNumber numberWithFloat:dialect.latitude],
         dialect.address,
         dialect.uri,
         [NSNumber numberWithInt:dialect.dateline],
         [NSNumber numberWithInt:dialect.good],
         [NSNumber numberWithInt:dialect.bad],
         [NSNumber numberWithInt:dialect.listen],
         [NSNumber numberWithInt:dialect.yuxiId],
         [NSNumber numberWithInt:dialect.sentenceId],
         dialect.distance,
         dialect.yuxi,
         dialect.content];
    }
        
    BOOL su = [db commit];
    if (!su) {
        NSLog(@"fail to add dialcts error %@", [db lastErrorMessage]);
    }
    [db close];
}



- (NSArray *)getRecordsWithCondition:(NSString *)condition orderBy:(NSString *)orderBy start:(NSInteger)start count:(NSInteger)count
{
    NSArray *dialects = [self select:@"*" condition:condition orderBy:orderBy start:start count:count];    
    return dialects;
}


- (NSArray *)select:(NSString *)select condition:(NSString *)condition orderBy:(NSString *)orderBy start:(NSInteger)start count:(NSInteger)count
{
    FMDatabase *db = [FMDatabase databaseWithPath:[PathHelper dbPath]];
    if (![db open]) {
        NSLog(@"getDialects: fail to open DB error  %@", [db lastErrorMessage]);
        return nil;
    }

    NSString *sql = [NSString stringWithFormat:@"select %@ from %@ where %@ order by %@ limit %d, %d", select, DialectTableName, condition, orderBy, start, count];
    FMResultSet *rs = [db executeQuery:sql];
    NSMutableArray *dialects = [[NSMutableArray alloc] initWithCapacity:20];
    while ([rs next]) {
        Dialect *d = [[Dialect alloc] init];
        d.dialectId = [rs intForColumn:DialectId];
        d.uid = [rs intForColumn:DialectUid];
        d.longitude = [rs doubleForColumn:DialectLongitude];
        d.latitude = [rs doubleForColumn:DialectLatitude];
        d.address = [rs stringForColumn:DialectAddress];
        d.uri = [rs stringForColumn:DialectUri];
        d.dateline = [rs intForColumn:DialectDateline];
        d.good = [rs intForColumn:DialectGood];
        d.bad = [rs intForColumn:DialectBad];
        d.listen = [rs intForColumn:DialectListen];
        d.yuxiId = [rs intForColumn:DialectYuXiId];
        d.sentenceId = [rs intForColumn:DialectSentenceId];
        d.distance = [rs stringForColumn:DialectDistance];
        d.yuxi = [rs stringForColumn:DialectYuXi];
        d.content = [rs stringForColumn:DialectContent];
        
        [dialects addObject:d];
        [d release];
    }
    
    [db close];
    
    return [dialects autorelease];
}

- (NSArray *)aroundWithLongitude:(float)lng latitude:(float)lat start:(NSInteger)start count:(NSInteger)count
{
    NSString *select = [NSString stringWithFormat:@"*, ((%f-%@)*(%f-%@) + (%f-%@)*(%f-%@)) as di", lng, DialectLongitude, lng, DialectLongitude, lat, DialectLatitude, lat, DialectLatitude];
    NSString *orderBy = @"di asc";
    NSArray *dialects = [self select:select condition:@"1" orderBy:orderBy start:start count:count];
    return dialects;
}

- (NSArray *)feedsFromStart:(NSInteger)start count:(NSInteger)count
{
    NSString *condition = [NSString stringWithFormat:@"%@ in (select %@ from %@ where %@=1)", DialectUid, UserUid, UserTableName, UserIsFollow];
    NSString *orderBy = [NSString stringWithFormat:@"%@ desc", DialectDateline];
    NSArray *dialects = [self select:@"*" condition:condition orderBy:orderBy start:start count:count];
    return dialects;
}

- (NSArray *)dialectsOfYuXi:(YuXi *)yuxi start:(NSInteger)start count:(NSInteger)count
{
    NSString *condition = [NSString stringWithFormat:@"%@=%d", DialectYuXiId, yuxi.yuxiId];
    NSString *orderBy = [NSString stringWithFormat:@"%@ desc", DialectDateline];
    NSArray *dialects = [self select:@"*" condition:condition orderBy:orderBy start:start count:count];
    return dialects;
}

- (NSArray *)dialectsOfUser:(User *)user start:(NSInteger)start count:(NSInteger)count
{
    NSString *condition = [NSString stringWithFormat:@"%@=%d", DialectUid, user.uid];
    NSString *orderBy = [NSString stringWithFormat:@"%@ desc", DialectDateline];
    NSArray *dialects = [self select:@"*" condition:condition orderBy:orderBy start:start count:count];
    return dialects;
}











@end
